USE DatingGame
GO
--#1 Find All the Boys
SELECT * 
FROM Boyfriends

--#2 Find a boy who is 6' tall
SELECT * 
FROM Boyfriends
WHERE Height = 6

--#3 Find a muscular boy who is under 6' tall
SELECT * 
FROM Boyfriends
WHERE Height < 6 
and Build = 'Muscular'

--#4 Find a slender boy with green eyes
SELECT * 
FROM Boyfriends
WHERE Build = 'Slender' 
and EyeColor = 'Green'

--#5 Find a boy under 25 with a romantic personality
SELECT * 
FROM Boyfriends
WHERE Age < 25 
and Personality = 'Romantic'

--#6 Find a musician who is not a pig
SELECT * 
FROM Boyfriends
WHERE Profession = 'Musician' 
and Personality != 'Pig'

--#7 Find a boy with Attractiveness Rating of 10 or
--one who is a sports star
SELECT * 
FROM Boyfriends
WHERE Attractiveness = 10
or Profession = 'Sports Star'

--#8 Find the youngest Danish boy 
SELECT Top 1 MIN(Age) As YoungAge, LastName, FirstName
FROM Boyfriends
WHERE Age is not Null and Nationality = 'Danish'
GROUP BY Age,  LastName, FirstName 
ORDER BY Age 

--#9 Count the number of boys that are single
SELECT COUNT(*) as ' Single Boys' 
FROM Boyfriends
WHERE IsSingle = 1

--#10 Find the average attractiveness of the actors
SELECT AVG(Attractiveness) as ' Average Attractiveness' 
FROM Boyfriends
WHERE Profession = 'Actor'

--#11 Find a boy with a red car
SELECT b.LastName, b.FirstName, c.Type
FROM Boyfriends b JOIN Cars c
ON b.CarID = c.ID
WHERE Color = 'Red'

--#12 Find a boy with a Hummer
SELECT b.LastName, b.FirstName, c.Type 
FROM Boyfriends b JOIN Cars c
ON b.CarID = c.ID
WHERE Type = 'Hummer'

--#13 Find a musician with a limo
SELECT b.LastName, b.FirstName, c.Type 
FROM Boyfriends b JOIN Cars c
ON b.CarID = c.ID
WHERE Type = 'Limo'
and Profession = 'Musician'

--#14 Find a romantic boy with a bicycle
SELECT b.LastName, b.FirstName, c.Type  
FROM Boyfriends b JOIN Cars c
ON b.CarID = c.ID
WHERE Type = 'Bicycyle'
and Personality = 'Romantic'

--*Who Dated Who**--

--#1 Who Dated Who?
SELECT b.FirstName,b.LastName,g.FirstName, g.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID

--#2 Who Dated Kristen Stewart?
SELECT b.FirstName,b.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where g.LastName = 'Stewart'

--#3 Who Dated Brad Pitt?
SELECT g.FirstName, g.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where b.LastName = 'Pitt'

--#4 Who is currently married?
SELECT b.FirstName,b.LastName,g.FirstName, g.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where d.CurrentStatus = 'Married'

--#4 Which couples are the same age?
SELECT b.FirstName,b.LastName,g.FirstName, g.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where b.Age = g.Age

--#5 Which girl is dating a man 
--16 years younger than her?
SELECT g.FirstName, g.LastName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where b.Age = (g.Age - 16)

--(NEW) #6 Which boys broke up the most 
--and how many times did they break up?
SELECT COUNT (d.WhoBrokeUpWithWho) AS 'Number of Breakups'
, b.FirstName,b.LastName, d.WhoBrokeUpWithWho
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Where d.WhoBrokeUpWithWho = 'boy'
GROUP BY  b.LastName, b.FirstName,  d.WhoBrokeUpWithWho
ORDER BY COUNT (d.WhoBrokeUpWithWho)DESC

--(NEW) #7 Which boy has the best average quality of dates 
SELECT TOP 1 AVG(d.QualityOfDate) AS ' Quality Dating Boy', b.LastName, b.FirstName
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Group By (d.QualityOfDate), b.LastName, b.FirstName
order by AVG(d.QualityOfDate)DESC

--#8 Which boy has dated the most girls?
SELECT Top 1 COUNT (d.id) as 'Number of dates', b.LastName, b.FirstName AS 'Speed Dater Girl'
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Group By b.LastName, b.FirstName
Having COUNT(d.ID) > 0
ORDER BY COUNT(d.id) DESC

--#8 Which girl has dated the most boys?
SELECT Top 1 count (d.id) as 'Number of dates', g.LastName, g.FirstName AS 'Speed Dater Girl'
FROM Boyfriends b JOIN WhoDatedWho d
ON b.ID = d.BoyID 
JOIN Girlfriends g
ON g.ID = d.GirlID
Group By g.LastName, g.FirstName
Having COUNT(d.ID) > 0
ORDER BY COUNT(d.id) DESC